using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;
using WinApp.Models;
using Serilog;

namespace WinApp.Services
{
    /// <summary>
    /// 数据库访问助手类
    /// </summary>
    public static class DataHelper
    {
        private static readonly string DatabasePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Data", "AppDatabase.db");
        private static readonly object _lock = new object();
        private static SqlSugarScope _db;

        /// <summary>
        /// 获取数据库连接实例
        /// </summary>
        public static SqlSugarScope Db
        {
            get
            {
                if (_db == null)
                {
                    lock (_lock)
                    {
                        if (_db == null)
                        {
                            InitializeDatabase();
                        }
                    }
                }
                return _db;
            }
        }

        /// <summary>
        /// 初始化数据库
        /// </summary>
        private static void InitializeDatabase()
        {
            try
            {
                // 确保Data目录存在
                var dataDir = Path.GetDirectoryName(DatabasePath);
                if (!Directory.Exists(dataDir))
                {
                    Directory.CreateDirectory(dataDir);
                }

                // 初始化数据库连接
                _db = new SqlSugarScope(new ConnectionConfig()
                {
                    DbType = DbType.Sqlite,
                    ConnectionString = $"Data Source={DatabasePath}",
                    IsAutoCloseConnection = true,
                    InitKeyType = InitKeyType.Attribute
                });

                // 创建数据库表
                _db.CodeFirst.InitTables<ActionClick>();
                _db.CodeFirst.InitTables<ActionOcr>();
                _db.CodeFirst.InitTables<ActionImageMatch>();
                _db.CodeFirst.InitTables<Avatar>();
                _db.CodeFirst.InitTables<ActionGroup>();
                _db.CodeFirst.InitTables<ActionGroupItem>();

                Log.Information("数据库初始化成功");
            }
            catch (Exception ex)
            {
                Log.Error(ex, "数据库初始化失败");
                throw;
            }
        }

        #region ActionClick操作
        /// <summary>
        /// 插入点击操作
        /// </summary>
        public static bool InsertActionClick(ActionClick action)
        {
            try
            {
                return Db.Insertable(action).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "插入点击操作失败");
                throw;
            }
        }

        /// <summary>
        /// 更新点击操作
        /// </summary>
        public static bool UpdateActionClick(ActionClick action)
        {
            try
            {
                return Db.Updateable(action).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "更新点击操作失败");
                throw;
            }
        }

        /// <summary>
        /// 删除点击操作
        /// </summary>
        public static bool DeleteActionClick(int id)
        {
            try
            {
                return Db.Deleteable<ActionClick>().Where(it => it.Id == id).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "删除点击操作失败");
                throw;
            }
        }

        /// <summary>
        /// 获取所有点击操作，按名称排序
        /// </summary>
        public static List<ActionClick> GetActionClicks(string filter = "")
        {
            try
            {
                if(filter.Trim() == "")
                    return Db.Queryable<ActionClick>().OrderBy(it => it.Name).ToList();
                else
                    return Db.Queryable<ActionClick>().Where(it => it.Name.Contains(filter)).OrderBy(it => it.Name).ToList();
            }
            catch (Exception ex)
            {
                Log.Error(ex, "获取点击操作列表失败");
                throw;
            }
        }

        /// <summary>
        /// 根据ID获取点击操作
        /// </summary>
        public static ActionClick GetActionClickById(int id)
        {
            try
            {
                return Db.Queryable<ActionClick>()
                    .First(it => it.Id == id);
            }
            catch (Exception ex)
            {
                Log.Error(ex, "根据ID获取点击操作失败");
                throw;
            }
        }
        #endregion

        #region ActionOcr操作
        /// <summary>
        /// 插入OCR操作
        /// </summary>
        public static bool InsertActionOcr(ActionOcr action)
        {
            try
            {
                return Db.Insertable(action).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "插入OCR操作失败");
                throw;
            }
        }

        /// <summary>
        /// 更新OCR操作
        /// </summary>
        public static bool UpdateActionOcr(ActionOcr action)
        {
            try
            {
                return Db.Updateable(action).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "更新OCR操作失败");
                throw;
            }
        }

        /// <summary>
        /// 删除OCR操作
        /// </summary>
        public static bool DeleteActionOcr(int id)
        {
            try
            {
                return Db.Deleteable<ActionOcr>().Where(it => it.Id == id).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "删除OCR操作失败");
                throw;
            }
        }

        /// <summary>
        /// 获取所有OCR操作
        /// </summary>
        public static List<ActionOcr> GetActionOcrs(string filter = "")
        {
            try
            {
                if(filter.Trim() == "")
                    return Db.Queryable<ActionOcr>()
                        .OrderBy(it => it.Name)
                        .Includes(x => x.ActionImageMatches)
                        .ToList();
                else
                    return Db.Queryable<ActionOcr>()
                        .Where(it => it.Name.Contains(filter))
                        .OrderBy(it => it.Name)
                        .Includes(x => x.ActionImageMatches)
                        .ToList();
            }
            catch (Exception ex)
            {
                Log.Error(ex, "获取OCR操作列表失败");
                throw;
            }
        }

        /// <summary>
        /// 根据ID获取OCR操作
        /// </summary>
        public static ActionOcr GetActionOcrById(int id)
        {
            try
            {
                return Db.Queryable<ActionOcr>()
                    .Includes(x => x.ActionImageMatches)
                    .First(it => it.Id == id);
            }
            catch (Exception ex)
            {
                Log.Error(ex, "根据ID获取OCR操作失败");
                throw;
            }
        }

        /// <summary>
        /// 获取名称中包含"区域"的所有OCR操作
        /// </summary>
        /// <returns>返回名称中包含"区域"的OCR操作列表</returns>
        public static List<ActionOcr> GetActionOcrRegions()
        {
            try
            {
                return Db.Queryable<ActionOcr>()
                    .Where(it => it.Name.Contains("区域"))
                    .Includes(x => x.ActionImageMatches)
                    .OrderBy(it => it.Name)
                    .ToList();
            }
            catch (Exception ex)
            {
                Log.Error(ex, "获取区域OCR操作列表失败");
                throw;
            }
        }

        public static List<ActionOcr> GetActionOcrNotRegions()
        {
            try
            {
                return Db.Queryable<ActionOcr>()
                    .Where(it => !it.Name.Contains("区域"))
                    .Includes(x => x.ActionImageMatches)
                    .OrderBy(it => it.Name)
                    .ToList();
            }
            catch (Exception ex)
            {
                Log.Error(ex, "获取区域OCR操作列表失败");
                throw;
            }
        }
        #endregion

        #region ActionImageMatch操作
        /// <summary>
        /// 插入图像匹配操作
        /// </summary>
        public static bool InsertActionImageMatch(ActionImageMatch action)
        {
            try
            {
                return Db.Insertable(action).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "插入图像匹配操作失败");
                throw;
            }
        }

        /// <summary>
        /// 更新图像匹配操作
        /// </summary>
        public static bool UpdateActionImageMatch(ActionImageMatch action)
        {
            try
            {
                return Db.Updateable(action).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "更新图像匹配操作失败");
                throw;
            }
        }

        /// <summary>
        /// 删除图像匹配操作
        /// </summary>
        public static bool DeleteActionImageMatch(int id)
        {
            try
            {
                return Db.Deleteable<ActionImageMatch>().Where(it => it.Id == id).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "删除图像匹配操作失败");
                throw;
            }
        }

        /// <summary>
        /// 获取所有图像匹配操作
        /// </summary>
        public static List<ActionImageMatch> GetActionImageMatches(string filter = "")
        {
            try
            {
                if(filter.Trim() == "")
                    return Db.Queryable<ActionImageMatch>()
                        .Includes(x => x.Avatar)
                        .Includes(x => x.ActionOcr)
                        .ToList();
                else
                    return Db.Queryable<ActionImageMatch>()
                        .Where(it => it.Name.Contains(filter))
                        .Includes(x => x.Avatar)
                        .Includes(x => x.ActionOcr)
                        .ToList();
            }
            catch (Exception ex)
            {
                Log.Error(ex, "获取图像匹配操作列表失败");
                throw;
            }
        }

        public static ActionImageMatch GetActionImageMatchById(int id)
        {
            try
            {
                return Db.Queryable<ActionImageMatch>()
                    .Includes(x => x.Avatar)
                    .Includes(x => x.ActionOcr)
                    .First(it => it.Id == id);
            }
            catch (Exception ex)
            {
                Log.Error(ex, "根据ID获取图像匹配操作失败");
                throw;
            }
        }
        #endregion

        #region Avatar操作
        /// <summary>
        /// 插入头像
        /// </summary>
        public static bool InsertAvatar(Avatar avatar)
        {
            try
            {
                return Db.Insertable(avatar).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "插入头像失败");
                throw;
            }
        }

        /// <summary>
        /// 更新头像
        /// </summary>
        public static bool UpdateAvatar(Avatar avatar)
        {
            try
            {
                return Db.Updateable(avatar).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "更新头像失败");
                throw;
            }
        }

        /// <summary>
        /// 删除头像
        /// </summary>
        public static bool DeleteAvatar(int id)
        {
            try
            {
                return Db.Deleteable<Avatar>().Where(it => it.Id == id).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "删除头像失败");
                throw;
            }
        }

        /// <summary>
        /// 获取所有头像
        /// </summary>
        public static List<Avatar> GetAvatars()
        {
            try
            {
                return Db.Queryable<Avatar>()
                    .Includes(x => x.ActionImageMatches)
                    .ToList();
            }
            catch (Exception ex)
            {
                Log.Error(ex, "获取头像列表失败");
                throw;
            }
        }

        /// <summary>
        /// 根据ID获取头像
        /// </summary>
        public static Avatar GetAvatarById(int id)
        {
            try
            {
                return Db.Queryable<Avatar>()
                    .Includes(x => x.ActionImageMatches)
                    .First(it => it.Id == id);
            }
            catch (Exception ex)
            {
                Log.Error(ex, "获取头像失败");
                throw;
            }
        }

        /// <summary>
        /// 根据名称获取头像
        /// </summary>
        public static Avatar GetAvatarByName(string name)
        {
            try
            {
                return Db.Queryable<Avatar>()
                    .Includes(x => x.ActionImageMatches)
                    .First(it => it.Name == name);
            }
            catch (Exception ex)
            {
                Log.Error(ex, "根据名称获取头像失败");
                throw;
            }
        }
        #endregion

        #region ActionGroup操作
        /// <summary>
        /// 插入动作组
        /// </summary>
        public static bool InsertActionGroup(ActionGroup action)
        {
            try
            {
                return Db.Insertable(action).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "插入动作组失败");
                throw;
            }
        }

        /// <summary>
        /// 更新动作组
        /// </summary>
        public static bool UpdateActionGroup(ActionGroup action)
        {
            try
            {
                return Db.Updateable(action).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "更新动作组失败");
                throw;
            }
        }

        /// <summary>
        /// 删除动作组
        /// </summary>
        /// <returns>删除结果，如果被引用则返回false</returns>
        public static bool DeleteActionGroup(int id)
        {
            try
            {
                // 检查该ActionGroup是否被其他ActionGroupItem引用
                var isReferenced = Db.Queryable<ActionGroupItem>()
                    .Any(it => it.ActionId == id && it.ActionType == ActionType.ActionGroup);

                if (isReferenced)
                {
                    Log.Warning($"动作组(ID={id})被其他动作组引用，无法删除");
                    return false;
                }

                // 首先删除关联的ActionGroupItems
                Db.Deleteable<ActionGroupItem>().Where(it => it.ActionGroupId == id).ExecuteCommand();
                // 然后删除ActionGroup
                return Db.Deleteable<ActionGroup>().Where(it => it.Id == id).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "删除动作组失败");
                throw;
            }
        }

        /// <summary>
        /// 获取所有动作组
        /// </summary>
        public static List<ActionGroup> GetActionGroups()
        {
            try
            {
                return Db.Queryable<ActionGroup>()
                    .OrderByDescending(it => it.IsDaily)
                    .OrderBy(it => it.Name)
                    .Includes(x => x.ActionGroupItems)
                    .ToList();
            }
            catch (Exception ex)
            {
                Log.Error(ex, "获取动作组列表失败");
                throw;
            }
        }

        /// <summary>
        /// 根据ID获取动作组
        /// </summary>
        public static ActionGroup GetActionGroupById(int id)
        {
            try
            {
                return Db.Queryable<ActionGroup>()
                    .Includes(x => x.ActionGroupItems)
                    .First(it => it.Id == id);
            }
            catch (Exception ex)
            {
                Log.Error(ex, "根据ID获取动作组失败");
                throw;
            }
        }
        #endregion

        #region ActionGroupItem操作
        /// <summary>
        /// 插入动作组项
        /// </summary>
        public static bool InsertActionGroupItem(ActionGroupItem item)
        {
            try
            {
                // 开启事务
                Db.Ado.BeginTran();

                // 1. 查询当前最大 Sequence 值
                var maxSequence = Db.Queryable<ActionGroupItem>()
                                    .Where(x => x.ActionGroupId == item.ActionGroupId) // 根据 ActionGroupId 过滤
                                    .Max(x => x.Sequence);

                // 2. 如果新记录的 Sequence 小于等于当前最大 Sequence，则需要调整其他记录的 Sequence
                if (item.Sequence <= maxSequence)
                {
                    // 将大于等于新 Sequence 的记录的 Sequence 加 1
                    var itemsToUpdate = Db.Queryable<ActionGroupItem>()
                                          .Where(x => x.ActionGroupId == item.ActionGroupId && x.Sequence >= item.Sequence)
                                          .OrderByDescending(x => x.Sequence) // 按降序处理
                                          .ToList();

                    foreach (var updateItem in itemsToUpdate)
                    {
                        updateItem.Sequence += 1;
                        Db.Updateable(updateItem).ExecuteCommand();
                    }
                }

                // 3. 插入新记录
                Db.Insertable(item).ExecuteCommand();

                // 提交事务
                Db.Ado.CommitTran();
                return true;
            }
            catch (Exception ex)
            {
                // 回滚事务
                Db.Ado.RollbackTran();
                Log.Error(ex, "插入动作组项失败");
                throw;
            }
        }

        /// <summary>
        /// 批量插入动作组项
        /// </summary>
        public static bool InsertActionGroupItems(List<ActionGroupItem> items)
        {
            try
            {
                return Db.Insertable(items).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "批量插入动作组项失败");
                throw;
            }
        }

        /// <summary>
        /// 更新动作组项
        /// </summary>
        public static bool UpdateActionGroupItem(ActionGroupItem item)
        {
            try
            {
                // 开启事务
                Db.Ado.BeginTran();

                // 1. 查询原记录的 Sequence 值
                var oldSequence = Db.Queryable<ActionGroupItem>()
                                    .Where(x => x.Id == item.Id)
                                    .Select(x => x.Sequence)
                                    .First();

                if (oldSequence == item.Sequence)
                {
                    // 如果 Sequence 没有变化，直接更新其他字段
                    Db.Updateable(item).ExecuteCommand();
                    Db.Ado.CommitTran(); // 提交事务
                    return true;
                }

                // 2. 判断 Sequence 变化方向
                if (item.Sequence > oldSequence)
                {
                    // 情况1：Sequence 变大（例如 4 -> 6）
                    // 将 oldSequence + 1 到 item.Sequence 之间的记录的 Sequence 减 1
                    var itemsToUpdate = Db.Queryable<ActionGroupItem>()
                                          .Where(x => x.Sequence > oldSequence && x.Sequence <= item.Sequence && x.ActionGroupId == item.ActionGroupId)
                                          .OrderBy(x => x.Sequence) // 按升序处理
                                          .ToList();

                    foreach (var updateItem in itemsToUpdate)
                    {
                        updateItem.Sequence -= 1;
                        Db.Updateable(updateItem).ExecuteCommand();
                    }
                }
                else
                {
                    // 情况2：Sequence 变小（例如 4 -> 2）
                    // 将 item.Sequence 到 oldSequence - 1 之间的记录的 Sequence 加 1
                    var itemsToUpdate = Db.Queryable<ActionGroupItem>()
                                          .Where(x => x.Sequence >= item.Sequence && x.Sequence < oldSequence && x.ActionGroupId == item.ActionGroupId)
                                          .OrderByDescending(x => x.Sequence) // 按降序处理
                                          .ToList();

                    foreach (var updateItem in itemsToUpdate)
                    {
                        updateItem.Sequence += 1;
                        Db.Updateable(updateItem).ExecuteCommand();
                    }
                }

                // 3. 更新当前记录的 Sequence
                Db.Updateable(item).ExecuteCommand();

                // 提交事务
                Db.Ado.CommitTran();
                return true;
            }
            catch (Exception ex)
            {
                // 回滚事务
                Db.Ado.RollbackTran();
                Log.Error(ex, "更新动作组项失败");
                throw;
            }
        }

        /// <summary>
        /// 删除动作组项
        /// </summary>
        public static bool DeleteActionGroupItem(int id)
        {
            try
            {
                Db.Ado.BeginTran();
                ActionGroupItem agi = Db.Queryable<ActionGroupItem>()
                    .Where(it => it.Id == id)
                    .First();
                if (agi == null)
                {
                    Log.Error($"未找到要删除的ActionGroupItem记录：Id={id}");
                    return false;
                }
                bool rst = Db.Deleteable<ActionGroupItem>().Where(it => it.Id == id).ExecuteCommand() > 0;
                Db.Updateable<ActionGroupItem>()
                    .SetColumns(it => it.Sequence == it.Sequence - 1)
                    .Where(it => it.Sequence > agi.Sequence && it.ActionGroupId == agi.ActionGroupId)
                    .ExecuteCommand();
                Db.Ado.CommitTran();
                return rst;
            }
            catch (Exception ex)
            {
                Db.Ado.RollbackTran();
                Log.Error(ex, "删除动作组项失败");
                return false;
            }
        }

        /// <summary>
        /// 删除动作组的所有项
        /// </summary>
        public static bool DeleteActionGroupItemsByGroup(int actionGroupId)
        {
            try
            {
                return Db.Deleteable<ActionGroupItem>()
                    .Where(it => it.ActionGroupId == actionGroupId)
                    .ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Log.Error(ex, "删除动作组的所有项失败");
                throw;
            }
        }

        /// <summary>
        /// 获取动作组的所有项
        /// </summary>
        public static List<ActionGroupItem> GetActionGroupItems(int actionGroupId)
        {
            try
            {
                return Db.Queryable<ActionGroupItem>()
                    .Where(it => it.ActionGroupId == actionGroupId)
                    .OrderBy(it => it.Sequence)
                    .ToList();
            }
            catch (Exception ex)
            {
                Log.Error(ex, "获取动作组项列表失败");
                throw;
            }
        }

        public static List<ActionGroupItem> GetActionGroupItemsByGroup(int actionGroupId, int group)
        {
            try
            {
                return Db.Queryable<ActionGroupItem>()
                    .Where(it => it.ActionGroupId == actionGroupId && it.Group == group)
                    .OrderBy(it => it.Sequence)
                    .ToList();
            }
            catch (Exception ex)
            {
                Log.Error(ex, "获取动作组项列表失败");
                throw;
            }
        }

        public static List<ActionGroup> GetActionGroupsExclude(int actionGroupId, string filter = "")
        {
            try
            {
                if(filter.Trim() == "")
                    return Db.Queryable<ActionGroup>()
                        .Where(it => it.Id != actionGroupId)
                        .ToList();
                else
                    return Db.Queryable<ActionGroup>()
                        .Where(it => it.Id != actionGroupId && it.Name.Contains(filter))
                        .ToList();
            }
            catch (Exception ex)
            {
                Log.Error(ex, "获取动作组列表失败");
                throw;
            }
        }
        #endregion

        public static ActionDelay GetActionDelay(int seconds)
        {
            return new ActionDelay()
            {
                Id = seconds,
                Name = $"延迟{seconds}秒",
                Delay = seconds * 1000
            };
        }

        public static List<ActionDelay> GetActionDelays()
        {
            List<ActionDelay> ads = new List<ActionDelay>();
            for (int i = 1; i <= 10; i++)
            {
                ads.Add(new ActionDelay()
                {
                    Id = i,
                    Name = $"延迟{i}秒",
                    Delay = i * 1000
                });
            }
            return ads;
        }
    }
}
